﻿using System;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using SBACN.WebSite.Code;
using SBACN.WebSite.Models;
using SBACN.WebSite.Cache;

namespace SBACN.WebSite
{
    public partial class AddressBook : MemberBasePage
    {
        public int PageFlag { get; set; }
		public string CouncilName { get; set; }

        protected void Page_Load(object sender, EventArgs e)
        {
			string Code =Request.QueryString["st"].Substring(0,8);//父亲Code

			if (!string.IsNullOrWhiteSpace(Code))
			{
				
				var result = SystemMapCache.Instance.List.Where(p => (p.Code == Code)).ToList();
				foreach (var l in result)
				{
				   this.CouncilName = l.CText;
				}
			}

			if (!string.IsNullOrWhiteSpace(Request.QueryString["st"]))
			{

				var result = SystemMapCache.Instance.List.Where(p => (p.Code == Request.QueryString["st"])).ToList();
				foreach (var l in result)
				{
					this.CouncilName = this.CouncilName+"&nbsp;>&nbsp;"+ l.CText;
				}
			}
				BindData(1);
			
        }


        protected void BindData(int pageIndex)
        {
            List.Visible = true;
            lnkDown.Visible = true;
            string t = Request.QueryString["st"];
            if (!string.IsNullOrEmpty(t))
            {
                this.PageFlag = (pageIndex - 1) * this.Pager1.PageSize;
                int records = 0;
                Expression<Func<TB_Professional, bool>> f = (p => p.Type == t);
                Expression<Func<TB_Professional, int>> order = (c => c.ID);
                var resultQ = DAL.DALHelper.Instance.GetList<TB_Professional, int>(f, order, true, this.Pager1.PageSize, pageIndex, out records);

                if (resultQ != null && resultQ.Count > 0)
                {

                    this.List.DataSource = resultQ;
                    this.List.DataBind();
                    this.Pager1.RecordCount = records;
                    this.Pager1.InitControl();
                }
                else
                {
                    List.Visible = false;
                    lnkDown.Visible = false;
                }
            }
        }
        protected void BindSelectedPageIndexChanged(int pageIndex, int nextPage)
        {
            BindData(nextPage);
        }

        protected void lnkDown_Click(object sender, EventArgs e)
        {
            string t = Request.QueryString["st"];
            if (!string.IsNullOrEmpty(t))
            {
                Expression<Func<TB_Professional, bool>> f = (p => p.Type == t);
                Expression<Func<TB_Professional, int>> order = (c => c.ID);
                var resultQ = DAL.DALHelper.Instance.GetList<TB_Professional, int>(f, order, true);

				string strTmpFileName = "~/Template/专委会正副主任单位.xls";
				string strDownloadFileName = "~/Template/Down/专委会正副主任单位" + DateTime.Now.Ticks + ".xls";
                File.Copy(Server.MapPath(strTmpFileName), Server.MapPath(strDownloadFileName), true);


                string strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strDownloadFileName) + ";Extended Properties='Excel 8.0;HDR=Yes'";

                using (OleDbConnection conn = new OleDbConnection(strExcelConn))
                {
                    //var list = Data();
                    conn.Open();
                    //OleDbCommand cmd = new OleDbCommand("create table HRVacationType(ExamName varchar(50), name varchar(50),CardID varchar(50),CompanyName varchar(50))", conn);
                    //cmd.ExecuteNonQuery();
                    foreach (var l in resultQ)
                    {
                        string insertcmd = " INSERT INTO [Sheet1$] (职务,单位) VALUES ('" + l.Level + "','" + l.MemberName + "') ";
                        OleDbCommand excmd = new OleDbCommand(insertcmd, conn);
                        excmd.ExecuteNonQuery();
                    }
                    conn.Close();

                }
                Response.Redirect(strDownloadFileName);
            }

        }
    }
}